# views.py
from django.shortcuts import render, redirect, get_object_or_404
from django.views import View
from django.views.generic import DeleteView
from django.contrib import messages
from .models import DataSource, Field, Table
from .form import DataSourceForm, FieldForm, TableForm
from django.urls import reverse_lazy
import pymysql
from django.db import transaction
from django.http import JsonResponse
# views.py
from django.views.decorators.csrf import csrf_exempt
from django.utils.decorators import method_decorator
import json

# views.py
def datasource_tables_api(request, datasource_pk):
    tables = Table.objects.filter(data_source_id=datasource_pk)
    data = {
        "tables": [{"id": t.id, "name": t.name, "is_show": t.is_show} for t in tables]
    }
    return JsonResponse(data)

@csrf_exempt
def set_tables_show(request, datasource_pk):
    if request.method == 'POST':
        data = json.loads(request.body)
        show_ids = data.get('show_ids', [])
        # 先全部设为 False
        Table.objects.filter(data_source_id=datasource_pk).update(is_show=False)
        # 再把选中的设为 True
        Table.objects.filter(data_source_id=datasource_pk, id__in=show_ids).update(is_show=True)
        # messages.success(request, '表格显示状态已更新！')
        print("更新后的表格显示状态：", show_ids)
        print("更新后的表格显示状态：", list(Table.objects.filter(data_source_id=datasource_pk).values('id', 'is_show')))
        print("数据源ID：", datasource_pk)
    else:
        messages.error(request, '无效的请求方法！')
    return JsonResponse({'status': 'ok'})

@csrf_exempt
def set_fields_show(request, table_id):
    if request.method == 'POST':
        import json
        data = json.loads(request.body)
        show_ids = data.get('show_ids', [])
        # 先全部设为 False
        Field.objects.filter(table_id=table_id).update(is_show=False)
        # 再把选中的设为 True
        Field.objects.filter(table_id=table_id, id__in=show_ids).update(is_show=True)
        print("更新后的字段显示状态：", show_ids)
        print("更新后的字段显示状态：", list(Field.objects.filter(table_id=table_id).values('id', 'is_show')))
        print("表格ID：", table_id)
        return JsonResponse({'status': 'ok'})
    return JsonResponse({'status': 'error', 'msg': '无效的请求方法！'})

def table_fields_api(request, table_id):
    fields = Field.objects.filter(table_id=table_id)
    data = {
        "fields": [{"id": f.id, "name": f.name, "is_show": f.is_show} for f in fields]
    }
    return JsonResponse(data)

class DataSourceListView(View):
    def get(self, request):
        data_sources = DataSource.objects.all()
        return render(request, 'datasource_list.html', {'data_sources': data_sources})
    def post(self, request):
        pk = request.POST.get('pk')
        if not pk:
            # 新建逻辑
            form = DataSourceForm(request.POST)
        else:
            instance = get_object_or_404(DataSource, pk=pk)
            form = DataSourceForm(request.POST, instance=instance)
        print("原始POST数据:", request.POST)
        if form.is_valid():
            datasource = form.save()
            if datasource.engine == 'mysql':
                try:
                    conn = pymysql.connect(
                        host=datasource.host,
                        port=datasource.port or 3306,
                        user=datasource.user,
                        password=datasource.password,
                        database=datasource.name,  # 你可以加一个 db_name 字段
                        charset='utf8mb4'
                    )
                    cursor = conn.cursor()
                    # 获取所有表名
                    cursor.execute("SHOW TABLES;")
                    tables = [row[0] for row in cursor.fetchall()]
                    print("数据库表名：", tables)
                    # 获取每个表的字段名
                    # for table_name in tables:
                    #     cursor.execute(f"SHOW COLUMNS FROM `{table_name}`;")
                    #     columns = [row[0] for row in cursor.fetchall()]
                    #     print(f"表 {table_name} 字段：", columns)
                    with transaction.atomic():
                        for table_name in tables:
                            # 保存表
                            table_obj, created = Table.objects.get_or_create(
                                name=table_name,
                                data_source=datasource
                            )
                            # 获取字段
                            cursor.execute(f"SHOW COLUMNS FROM `{table_name}`;")
                            columns = cursor.fetchall()
                            for col in columns:
                                field_name = col[0]
                                # 这里只简单判断类型，可根据需要完善
                                mysql_type = col[1]
                                if 'int' in mysql_type:
                                    field_type = 'integer'
                                elif 'char' in mysql_type or 'text' in mysql_type:
                                    field_type = 'string'
                                elif 'float' in mysql_type or 'double' in mysql_type or 'decimal' in mysql_type:
                                    field_type = 'float'
                                elif 'date' in mysql_type and 'time' in mysql_type:
                                    field_type = 'datetime'
                                elif 'date' in mysql_type:
                                    field_type = 'date'
                                elif 'bool' in mysql_type or 'tinyint(1)' in mysql_type:
                                    field_type = 'boolean'
                                else:
                                    field_type = 'string'
                                Field.objects.get_or_create(
                                    name=field_name,
                                    table=table_obj,
                                    defaults={'field_type': field_type}
                                )
                    cursor.close()
                    conn.close()
                    messages.success(request, '数据源连接成功！')
                except Exception as e:
                    messages.error(request, '数据源连接失败！')
                    print("数据库连接或查询失败：", e)
            # messages.success(request, '数据源创建成功！')
        else:
            print("表单错误:", form.errors) 
            messages.error(request, '数据源创建失败！')
        return redirect('datasource_list')
    def delete(self, request, pk):
        data_source = get_object_or_404(DataSource, pk=pk)
        data_source.delete()
        messages.success(request, '数据源已删除！')
        return redirect('datasource_list')

class DataSourceCreateView(View):
    def get(self, request):
        form = DataSourceForm()
        return render(request, 'datasource_form.html', {'form': form})
    
    def post(self, request):
        form = DataSourceForm(request.POST)
        if form.is_valid():
            form.save()
            return redirect('datasource_list')
        return render(request, 'datasource_form.html', {'form': form})

class DataSourceUpdateView(View):
    def get(self, request, pk):
        data_source = get_object_or_404(DataSource, pk=pk)
        form = DataSourceForm(instance=data_source)
        return render(request, 'datasource_form.html', {'form': form, 'data_source': data_source})
    
    def post(self, request, pk):
        data_source = get_object_or_404(DataSource, pk=pk)
        form = DataSourceForm(request.POST, instance=data_source)
        if form.is_valid():
            form.save()
            messages.success(request, '数据源更新成功！')
            return redirect('datasource_list')
        return render(request, 'datasource_form.html', {'form': form, 'data_source': data_source})

class DataSourceDeleteView(DeleteView):
    model = DataSource
    success_url = reverse_lazy('datasource_list')

class TableListView(View):
    def get(self, request, datasource_pk):  
        data_source = get_object_or_404(DataSource, pk=datasource_pk)
        tables = data_source.tables.all()
        return render(request, 'table_list.html', {'data_source': data_source, 'tables': tables})
    def post(self, request, datasource_pk):
        form = TableForm(request.POST)
        print("原始POST数据:", request.POST)
        if form.is_valid():
            table = form.save(commit=False)
            table.save()
            # messages.success(request, '表格创建成功！')
        else:
            print("表单错误:", form.errors) 
            # messages.error(request, '表格创建失败！')
        return redirect('table_list', datasource_pk=datasource_pk)
    
    def delete(self, request, datasource_pk, pk):
        data_source = get_object_or_404(DataSource, pk=datasource_pk)
        table = get_object_or_404(Table, pk=pk, data_source=data_source)
        table.delete()
        messages.success(request, '表格已删除！')
        return redirect('table_list', datasource_pk=datasource_pk)

class TableCreateView(View):
    def get(self, request, datasource_pk):
        data_source = get_object_or_404(DataSource, pk=datasource_pk)
        form = TableForm(initial={'data_source': data_source})
        return render(request, 'table_form.html', {'form': form, 'data_source': data_source})

    def post(self, request, datasource_pk):
        data_source = get_object_or_404(DataSource, pk=datasource_pk)
        form = TableForm(request.POST)
        if form.is_valid():
            table = form.save(commit=False)
            table.data_source = data_source
            table.save()
            messages.success(request, '表格创建成功！')
            return redirect('table_list', datasource_pk=datasource_pk)
        return render(request, 'table_form.html', {'form': form, 'data_source': data_source})

class TableUpdateView (View):
    def get(self, request, datasource_pk, pk):
        data_source = get_object_or_404(DataSource, pk=datasource_pk)
        table = get_object_or_404(Table, pk=pk, data_source=data_source)
        form = TableForm(instance=table)
        return render(request, 'table_form.html', {'form': form, 'data_source': data_source, 'table': table})

    def post(self, request, datasource_pk, pk):
        data_source = get_object_or_404(DataSource, pk=datasource_pk)
        table = get_object_or_404(Table, pk=pk, data_source=data_source)
        form = TableForm(request.POST, instance=table)
        if form.is_valid():
            form.save()
            messages.success(request, '表格更新成功！')
            return redirect('table_list', datasource_pk=datasource_pk)
        return render(request, 'table_form.html', {'form': form, 'data_source': data_source, 'table': table})

class TableDeleteView(DeleteView):
    model = Table
    success_url = reverse_lazy('table_list')


class FieldListView(View):
    def get(self, request, datasource_pk, table_pk):
        data_source = get_object_or_404(DataSource, pk=datasource_pk)
        table = get_object_or_404(Table, pk=table_pk, data_source=data_source)
        fields = table.fields.all()
        return render(request, 'field_list.html', {'data_source': data_source, 'table': table, 'fields': fields})
    def post(self, request, datasource_pk, table_pk):
        data_source = get_object_or_404(DataSource, pk=datasource_pk)
        table = get_object_or_404(Table, pk=table_pk, data_source=data_source)
        form = FieldForm(request.POST)
        if form.is_valid():
            field = form.save(commit=False)
            field.table = table
            field.save()

        else:
            print("表单错误:", form.errors) 
            # messages.error(request, '数据源创建失败！')
        return redirect('field_list', datasource_pk=datasource_pk, table_pk=table_pk)